package com.culturesalon.dao;

import java.io.Serializable;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.culturesalon.connectionfactory.ConnectionFactory;
import com.culturesalon.dto.Goods;
import com.culturesalon.dto.Notice;

public class NoticeDAOImpl implements NoticeDAO {

	@Override
	public Notice get(Integer id) {
		Connection conn = null;
		PreparedStatement stmt = null;
		ResultSet rs = null;
		StringBuffer sql = new StringBuffer();
		sql.append(" SELECT id,title,date,content,author,recommend,categoryId");
		sql.append(" FROM T_NOTICE ");
		sql.append(" WHERE ID=? ");
		conn = ConnectionFactory.getConnection();
		Notice notice = null;
		try {
			stmt = conn.prepareStatement(sql.toString());
			stmt.setInt(1, id);
			rs = stmt.executeQuery();
			if (rs.next()) {
				notice = new Notice();
				notice.setId(rs.getInt("id"));
				notice.setTitle(rs.getString("title"));
				notice.setDate(rs.getDate("Date"));
				notice.setContent(rs.getString("content"));
				notice.setAuthor(rs.getString("author"));
				notice.setRecommend(rs.getBoolean("recommend"));
				notice.setCategoryId(rs.getInt("categoryId"));
				
				

			}
		} catch (SQLException e) {
			e.printStackTrace();
			return null;
		} finally {
			ConnectionFactory.close(conn, rs, stmt);
		}
		return notice;
	}


	@Override
	public Serializable save(Notice notice) {
		Connection conn = null;
		PreparedStatement pstm = null;
		StringBuffer sql = new StringBuffer();
		sql.append("INSERT INTO T_NOTICE(TITLE,DATE,CONTENT,AUTHOR,RECOMMEND,CATEGORYID)");
		
		sql.append("VALUES(?,?,?,?,?,?)");
		conn=ConnectionFactory.getConnection();
		int result=0;
		try {
			
			pstm = conn.prepareStatement(sql.toString());
			
			pstm.setString(1, notice.getTitle());
			pstm.setDate(2, new java.sql.Date(notice.getDate().getTime()));
			pstm.setString(3, notice.getContent());
			pstm.setString(4, notice.getAuthor());
			pstm.setBoolean(5, notice.getRecommend());
			pstm.setInt(6, notice.getCategoryId());
			
			result = pstm.executeUpdate();

			return result;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return 0;
		} finally {
			ConnectionFactory.close(conn, null, pstm);
		}

	}

	@Override
	public void update(Notice notice) {
		Connection conn = null;
		PreparedStatement pstm = null;
		StringBuffer sql = new StringBuffer();
		sql.append(" UPDATE T_NOTICE SET TITLE=?,DATE=?,CONTENT=?,AUTHOR=?,recommend=?,CATEGORYID=?");
		sql.append(" WHERE ID=?");
		try {
			conn = ConnectionFactory.getConnection();
			pstm = conn.prepareStatement(sql.toString());
			
			pstm.setString(1, notice.getTitle());
			pstm.setDate(2, new java.sql.Date(notice.getDate().getTime()));
			pstm.setString(3, notice.getContent());
			pstm.setString(4, notice.getAuthor());
			pstm.setBoolean(5, notice.getRecommend());
			pstm.setInt(6, notice.getCategoryId());;
			pstm.setInt(7, notice.getId());
			
			
			pstm.executeUpdate();

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			ConnectionFactory.close(conn, null, pstm);

		}

	}

	

	@Override
	public Serializable delete(Serializable id) {
		Connection conn = null;
		PreparedStatement pstm = null;
		StringBuffer sql = new StringBuffer();
		sql.append(" DELETE FROM T_NOTICE WHERE id=?");
		conn = ConnectionFactory.getConnection();
		try {
			pstm = conn.prepareStatement(sql.toString());
			pstm.setObject(1, id);

			int records = pstm.executeUpdate();
			return records;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return -1;
		} finally {
			ConnectionFactory.close(conn, null, pstm);
		}

	}


	@Override
	public List<Notice> findAll() {
		// TODO Auto-generated method stub
		List<Notice> list = new ArrayList<Notice>();
		Connection conn = null;
		PreparedStatement pstm = null;
		ResultSet rs = null;
		StringBuffer sql = new StringBuffer();
		sql.append(" SELECT * FROM T_NOTICE ");
		conn = ConnectionFactory.getConnection();
		Notice notice = null;
		try {
			pstm = conn.prepareStatement(sql.toString());
			rs = pstm.executeQuery();
			while (rs.next()) {
				notice = new Notice();
				notice.setId(rs.getInt("id"));
				notice.setTitle(rs.getString("title"));
				notice.setDate(rs.getDate("date"));
				notice.setContent(rs.getString("Content"));
				notice.setAuthor(rs.getString("author"));
				notice.setRecommend(rs.getBoolean("recommend"));
				notice.setCategoryId(rs.getInt("categoryId"));
				list.add(notice);

			}
			return list;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return null;
		} finally {
			ConnectionFactory.close(conn, rs, pstm);

		}

	}




	public List<Notice> findAllById(String Id, Integer number) {
		List<Notice> list=new ArrayList<Notice>();
		Connection conn=null;
		PreparedStatement pstm=null;
		ResultSet rs=null;
		StringBuffer sql=new StringBuffer();
		sql.append(" SELECT * FROM T_NOTICE WHERE CATEGORYID=? LIMIT ?");
		conn=ConnectionFactory.getConnection();
		Notice notice=null;
		try {
			pstm=conn.prepareStatement(sql.toString());
			pstm.setInt(1,Integer.parseInt(Id));
			pstm.setInt(2, number);
			rs=pstm.executeQuery();
			while(rs.next()){
				notice = new Notice();
				notice.setId(rs.getInt("id"));
				notice.setTitle(rs.getString("title"));
				notice.setDate(rs.getDate("date"));
				notice.setContent(rs.getString("Content"));
				notice.setAuthor(rs.getString("author"));
				notice.setRecommend(rs.getBoolean("recommend"));
				notice.setCategoryId(rs.getInt("categoryId"));
				list.add(notice);
				
			}
			return list;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return null;
		}finally{
			ConnectionFactory.close(conn, rs, pstm);
			
		}
	}



	

}
